在上一篇文章中，我們介紹了 PostgreSQL 的隔離層級（Isolation Levels），了解資料庫如何在多個交易同時進行時維持一致性。但實務上，光靠隔離層級並不足以避免所有的衝突與問題，這時就需要更細緻的控制方式：鎖（Lock）。

鎖的目的，是在多個交易同時讀寫資料時，防止資料不一致、競爭或衝突的情況發生。依照鎖的範圍，可以分成 Table Lock 以及 Row Lock：

• Table Lock：針對整張資料表加鎖。
• Row Lock：針對資料表中的特定 row 加鎖。

這篇文章會先從 Table Lock 開始，說明各種不同類型的鎖、它們在什麼時候會被觸發，以及如何觀察與測試它們。明天文章會再深入探討 Row Lock，了解它們的使用時機跟用法。

PostgreSQL 的 8 種 Table Lock

以 Table Lock 來說，在 PostgreSQL 中每次對資料表的存取（SELECT、INSERT、UPDATE、DELETE...）都會取得一種「鎖」，用來保證資料一致性與避免衝突。Table Lock 總共有 8 種，下面的表格是根據文件，整理出來的八種 Lock 出現的時機：

這 8 種 Table Lock 又各自與其他鎖可能互斥，這是 PostgreSQL 官方文件整理出來的 Table Lock 互斥的表，等下的實驗可以再回來看對照表格一起看。

https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES

接下來我想要做兩個實驗：

• ACCESS EXCLUSIVE 是層級最大的鎖，它和每一個其他鎖都互斥，看起來好像只要做跟改動 table schema 相關的操作就會拿到這個鎖，我想試試看是不是真的其他操作都沒辦法做。
• 之前 Day 18 我們有看到 CREATE INDEX CONCURRENTLY ，它可以讓建立 Index 時還能修改資料，我想試試看它拿到的 SHARE UPDATE EXCLUSIVE 鎖，是不是真的開放資料修改。

ACCESS EXCLUSIVE：權力最大的鎖

這是 PostgreSQL Table Lock 最嚴格的表鎖，只要執行 ALTER、DROP、TRUNCATE 等操作時會觸發，會阻擋所有其他存取。

1. 先建立測試資料表：

CREATE TABLE lock_test (
	id SERIAL PRIMARY KEY,
	value TEXT
);
INSERT INTO lock_test (value) VALUES ('a'), ('b'), ('c');

2. Session A: 拿到 ACCESS EXCLUSIVE lock

BEGIN;
ALTER TABLE lock_test ADD COLUMN dummy TEXT;
-- DO NOT COMMIT YET!

3. Session B: 嘗試讀取資料

可以看到等了 10 秒還是沒有回覆，因為被 Session A 的 ACCESS EXCLUSIVE 鎖擋住。

SELECT * FROM lock_test;

這時候可以利用 pg_lock 查到 lock_test 目前有的 lock。

SELECT pid, mode, relation::regclass, granted
FROM pg_locks
WHERE relation::regclass::text = 'lock_test';

SELECT 被擋住是因為要拿 ACCESS SHARE 的鎖，但是它與 ACCESS EXCLUSIVE 兩者衝突（可以參考表格）。這時候只要再 COMMIT 把 transaction 結束之後，ACCESS EXCLUSIVE 的鎖釋放，就可以正常 SELECT 了，也會看到新增的 dummy 欄位。

COMMIT;
SELECT * FROM lock_test;

SHARE UPDATE EXCLUSIVE：讓 CONCURRENTLY 能開放修改的鎖

SHARE UPDATE EXCLUSIVE 真的可以讓 table 開放修改資料嗎？根據文件 CREATE INDEX CONCURRENTLY 是拿到這個鎖，我們可以用 LOCK 指令來模擬這件事。

1. 模擬 CREATE INDEX CONCURRENTLY ，把 table 鎖住，試試看更改裡面的資料

Session A - 鎖表

BEGIN;
LOCK TABLE lock_test IN SHARE UPDATE EXCLUSIVE MODE;

Session B - 嘗試更改資料

BEGIN;
UPDATE lock_test SET value = 'd' WHERE id = 1;

UPDATE 成功了，那如果是用 CREATE INDEX 的 SHARE 鎖，也可以做一樣的事嗎？記得先把上面這兩個 transaction COMMIT 或 ROLLBACK 再繼續往下。

2. 模擬 CREATE INDEX ，把 table 鎖住，試試看更改裡面的資料

Session A - 鎖表

BEGIN;
LOCK TABLE lock_test IN SHARE MODE;

Session B - 嘗試修改

BEGIN;
UPDATE lock_test SET value = 'e' WHERE id = 1;

發現這個 UPDATE 真的就卡住了，沒辦法更改。這時候再回去 Session A COMMIT; ，代表 Session A 把鎖釋放，再看 Session B 就會發現成功更改了，但是他花了 36 秒的時間，因為剛剛一直在等 Session A 的鎖。

是誰擋住我？

有時候不知道為什麼卡住的時候，可以利用 pg_stat_activity 以及 pg_blocking_pids 這兩張 table 來幫忙。

在 pg_stat_activity 中有一個 state 欄位，如果像剛剛那樣執行一次最後一個實驗，查詢時就可以找到 idle in transaction 的 狀態，並且後面可以看到相對的 query。

除了 state 以外，也有 xact_start 可以得知 transaction 開始的時間，以及這個操作的 pid 。

而 pg_blocking_pids 是用來查哪一個操作擋住我，比如我可以先從 pg_stat_activity 找到 UPDATE 指令 的 pid 是 84663，我就可以用這個 84663 去找哪一個 pid 擋住我。

pg_blocking_pids 會回傳一個陣列，裡面是所有擋住這個操作的 pids。像是我找到是 83934 擋住，就可以再回去 pg_stat_activity 看是哪一個 query 。也可以兩張表 join 之後選擇想要看的欄位，就會很清楚知道資料庫目前發生什麼事情了！

SELECT
    a.pid, -- 目前操作
    a.usename,
    a.state,
    a.query,
    age(now(), a.query_start) AS query_age, -- 計算已執行多久
    b.pid AS blocking_pid, -- 被誰擋住
    b.usename AS blocking_user,
    b.query AS blocking_query -- 擋住的 query 是什麼
FROM pg_stat_activity a
JOIN pg_stat_activity b ON b.pid = ANY(pg_blocking_pids(a.pid))

不過雖然 Table Lock 能保護整張資料表的完整性，但在實務上，如果每一個鎖都要鎖到一整張表，非常容易造成等待與效能瓶頸。因此也會需要使用到 Row Lock，只鎖定實際被操作的 row，避免不必要的阻塞。

明天我們就來看看 Row Level Lock 有哪些，那就明天見了～

重點回顧

• PostgreSQL 中總共有 8 種不同層級的 Table Lock，會在操作 table 改變時自動產生，用來保護資料一致性
• Table Lock 是為了保護資料一致性，但也可能造成併發瓶頸
• 不確定有哪些操作卡住資料庫時，可以利用 pg_blocking_pids 以及 pg_stat_activity 這兩張 table 來查詢。

參考資料

https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES
https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
https://www.postgresql.org/docs/9.6/functions-info.html